using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using database;
using objetos;
using System.IO;
using System.Xml.Serialization;

namespace dados
{
	#region TB_NF_ITEM
	/// <summary>
	/// This object represents the properties and methods of a TB_NF_ITEM.
	/// </summary>
	public class DAL_TB_NF_ITEM
	{		
	
		/// <summary>
		/// Incluir via aplicacao
		/// </summary>	
		public int Incluir(OBJ_TB_NF_ITEM obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				DataSet ds = new DataSet();
				List<string> lista = new List<string>();		
	
				db.CreateParameter("@id_nf", SqlDbType.Int, obj.id_nf);				
				lista.Add("@id_nf");				
				db.CreateParameter("@id_produto_servico", SqlDbType.Int, obj.id_produto_servico);				
				lista.Add("@id_produto_servico");				
				db.CreateParameter("@id_tipo_item", SqlDbType.Int, obj.id_tipo_item);				
				lista.Add("@id_tipo_item");				
				db.CreateParameter("@ean", SqlDbType.VarChar, obj.ean);				
				lista.Add("@ean");				
				db.CreateParameter("@descricao", SqlDbType.VarChar, obj.descricao);				
				lista.Add("@descricao");				
				db.CreateParameter("@id_ncm", SqlDbType.Int, obj.id_ncm);				
				lista.Add("@id_ncm");				
				db.CreateParameter("@extipi", SqlDbType.VarChar, obj.extipi);				
				lista.Add("@extipi");				
				db.CreateParameter("@id_genero_produto_servico", SqlDbType.Int, obj.id_genero_produto_servico);				
				lista.Add("@id_genero_produto_servico");				
				db.CreateParameter("@cfop", SqlDbType.Int, obj.cfop);				
				lista.Add("@cfop");				
				db.CreateParameter("@qtd", SqlDbType.Decimal, obj.qtd);				
				lista.Add("@qtd");				
				db.CreateParameter("@qtd_trib", SqlDbType.Decimal, obj.qtd_trib);				
				lista.Add("@qtd_trib");				
				db.CreateParameter("@id_unidade_medida", SqlDbType.Int, obj.id_unidade_medida);				
				lista.Add("@id_unidade_medida");				
				db.CreateParameter("@desc_unid_medida", SqlDbType.VarChar, obj.desc_unid_medida);				
				lista.Add("@desc_unid_medida");				
				db.CreateParameter("@desc_unid_medida_trib", SqlDbType.VarChar, obj.desc_unid_medida_trib);				
				lista.Add("@desc_unid_medida_trib");				
				db.CreateParameter("@valor_unitario", SqlDbType.Decimal, obj.valor_unitario);				
				lista.Add("@valor_unitario");				
				db.CreateParameter("@valor_unitario_trib", SqlDbType.Decimal, obj.valor_unitario_trib);				
				lista.Add("@valor_unitario_trib");				
				db.CreateParameter("@valor_frete", SqlDbType.Decimal, obj.valor_frete);				
				lista.Add("@valor_frete");				
				db.CreateParameter("@valor_seguro", SqlDbType.Decimal, obj.valor_seguro);				
				lista.Add("@valor_seguro");				
				db.CreateParameter("@valor_desconto", SqlDbType.Decimal, obj.valor_desconto);				
				lista.Add("@valor_desconto");				
				db.CreateParameter("@valor_outro", SqlDbType.Decimal, obj.valor_outro);				
				lista.Add("@valor_outro");				
				db.CreateParameter("@valor_total", SqlDbType.Decimal, obj.valor_total);				
				lista.Add("@valor_total");				
				db.CreateParameter("@ind_total_nota", SqlDbType.Int, obj.ind_total_nota);				
				lista.Add("@ind_total_nota");				
	
				string query = String.Format("Insert Into tb_nf_item ({0}) Values ({1}); SELECT SCOPE_IDENTITY()",  string.Join(",",lista.ToArray()).ToString().Replace("@", ""), string.Join(",",lista.ToArray()).ToString());
				ds = db.ExecuteQuery(query);
				
				return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
			}
			catch (Exception)
       		{
            	throw;
        	}
		}


      /// <summary>
      /// Calcula Total Nota fiscal 
      /// </summary>
      /// <param name="id_nf"></param>
        public void AtualizaTotalNotaFiscal(int id_nf, int id_operacao)
        {
            try
            {
                DAO_DATABASE db = new DAO_DATABASE();
                StringBuilder sb = new StringBuilder();

                sb.Append(" update tb_nf set  ");
                sb.Append(" valor_total_bc_icms =  (select isnull(sum(valor_bc_icms),0) from tb_nf_item_imposto_icms where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_icms = ( select isnull(sum(valor_icms),0) from tb_nf_item_imposto_icms where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_bc_icms_st = (select  isnull(sum(valor_bc_icms_st),0) from tb_nf_item_imposto_icms where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_icms_st = (select  isnull(sum(valor_icms_st),0) from tb_nf_item_imposto_icms where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_produto =  ( select isnull(SUM(valor_unitario),0) from tb_nf_item where id_nf = @id_nf and id_tipo_item <> 9 ),  ");
                sb.Append(" valor_total_frete  =   ( select isnull(SUM(valor_frete),0) from tb_nf_item where id_nf = @id_nf and id_tipo_item <> 9 ), ");
                sb.Append(" valor_total_seguro =   ( select isnull(SUM(valor_seguro),0) from tb_nf_item where id_nf = @id_nf and id_tipo_item <> 9 ),  ");
                sb.Append(" valor_total_desconto = ( select isnull(SUM(valor_desconto),0) from tb_nf_item where id_nf = @id_nf and id_tipo_item <> 9 ), "); 
                sb.Append(" valor_total_outro =  ( select isnull(SUM(valor_outro),0) from tb_nf_item where id_nf = @id_nf and id_tipo_item <> 9 ),  ");
                sb.Append(" valor_total_ii = (select isnull(sum(valor_ii),0) from tb_nf_item_imposto_ii where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_ipi = (select isnull(sum(valor_ipi),0) from tb_nf_item_imposto_ipi where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_pis = (select isnull(sum(valor_pis),0) from tb_nf_item_imposto_pis where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_pis_st = (select isnull(sum(valor_pis_st),0) from tb_nf_item_imposto_pis_st where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_cofins = (select isnull(sum(valor_cofins),0) from tb_nf_item_imposto_cofins where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_cofins_st = (select isnull(sum(valor_cofins_st),0) from tb_nf_item_imposto_cofins_st where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)), ");
                sb.Append(" valor_total_nota_fiscal =  ( select isnull(SUM(valor_total),0) from tb_nf_item where id_nf = @id_nf and id_tipo_item <> 9 ), ");
                sb.Append(" valor_total_bc_issqn = (select isnull(sum(valor_bc_issqn),0) from tb_nf_item_imposto_issqn where id_nf_item in (select id from tb_nf_item where id_nf = @id_nf)),");
                sb.Append(" valor_total_servico = ( select isnull(SUM(valor_unitario),0) from tb_nf_item where id_nf = @id_nf and id_tipo_item = 9 )  ");               
                sb.Append(" WHERE id=@id_nf  ");



                db.CreateParameter("@id_nf", SqlDbType.Int, id_nf);
                db.ExecuteQuery(sb.ToString());

            }
            catch (Exception)
            {

                throw;
            }

        }
		
		/// <summary>
		/// Update via aplicacao
		/// </summary>			
		public void Alterar(OBJ_TB_NF_ITEM obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				List<string> lista = new List<string>();
	
				db.CreateParameter("@id", SqlDbType.Int, obj.id);
				
	
				db.CreateParameter("@id_nf", SqlDbType.Int, obj.id_nf);
				lista.Add("id_nf = @id_nf");
				db.CreateParameter("@id_produto_servico", SqlDbType.Int, obj.id_produto_servico);
				lista.Add("id_produto_servico = @id_produto_servico");
				db.CreateParameter("@id_tipo_item", SqlDbType.Int, obj.id_tipo_item);
				lista.Add("id_tipo_item = @id_tipo_item");
				db.CreateParameter("@ean", SqlDbType.VarChar, obj.ean);
				lista.Add("ean = @ean");
				db.CreateParameter("@descricao", SqlDbType.VarChar, obj.descricao);
				lista.Add("descricao = @descricao");
				db.CreateParameter("@id_ncm", SqlDbType.Int, obj.id_ncm);
				lista.Add("id_ncm = @id_ncm");
				db.CreateParameter("@extipi", SqlDbType.VarChar, obj.extipi);
				lista.Add("extipi = @extipi");
				db.CreateParameter("@id_genero_produto_servico", SqlDbType.Int, obj.id_genero_produto_servico);
				lista.Add("id_genero_produto_servico = @id_genero_produto_servico");
				db.CreateParameter("@cfop", SqlDbType.Int, obj.cfop);
				lista.Add("cfop = @cfop");
				db.CreateParameter("@qtd", SqlDbType.Decimal, obj.qtd);
				lista.Add("qtd = @qtd");
				db.CreateParameter("@qtd_trib", SqlDbType.Decimal, obj.qtd_trib);
				lista.Add("qtd_trib = @qtd_trib");
				db.CreateParameter("@id_unidade_medida", SqlDbType.Int, obj.id_unidade_medida);
				lista.Add("id_unidade_medida = @id_unidade_medida");
				db.CreateParameter("@desc_unid_medida", SqlDbType.VarChar, obj.desc_unid_medida);
				lista.Add("desc_unid_medida = @desc_unid_medida");
				db.CreateParameter("@desc_unid_medida_trib", SqlDbType.VarChar, obj.desc_unid_medida_trib);
				lista.Add("desc_unid_medida_trib = @desc_unid_medida_trib");
				db.CreateParameter("@valor_unitario", SqlDbType.Decimal, obj.valor_unitario);
				lista.Add("valor_unitario = @valor_unitario");
				db.CreateParameter("@valor_unitario_trib", SqlDbType.Decimal, obj.valor_unitario_trib);
				lista.Add("valor_unitario_trib = @valor_unitario_trib");
				db.CreateParameter("@valor_frete", SqlDbType.Decimal, obj.valor_frete);
				lista.Add("valor_frete = @valor_frete");
				db.CreateParameter("@valor_seguro", SqlDbType.Decimal, obj.valor_seguro);
				lista.Add("valor_seguro = @valor_seguro");
				db.CreateParameter("@valor_desconto", SqlDbType.Decimal, obj.valor_desconto);
				lista.Add("valor_desconto = @valor_desconto");
				db.CreateParameter("@valor_outro", SqlDbType.Decimal, obj.valor_outro);
				lista.Add("valor_outro = @valor_outro");
				db.CreateParameter("@valor_total", SqlDbType.Decimal, obj.valor_total);
				lista.Add("valor_total = @valor_total");
				db.CreateParameter("@ind_total_nota", SqlDbType.Int, obj.ind_total_nota);
				lista.Add("ind_total_nota = @ind_total_nota");
	
				string query = String.Format("Update TB_NF_ITEM Set {0} Where id = @id", string.Join(",",lista.ToArray()).ToString());
				db.ExecuteQuery(query);
			}
			catch (Exception)
       		{
            	throw;
        	}
		}	
		
		/// <summary>
		/// Delete viaaplicacao
		/// </summary>	
		public void Excluir(OBJ_TB_NF_ITEM obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				StringBuilder queryParameters = new StringBuilder();
				db.CreateParameter("@id", SqlDbType.Int, obj.id);
		
				string query = "Delete tb_nf_item Where id = @id";			
				db.ExecuteQuery(query);
			}
			catch (Exception)
       		{
            	throw;
        	}
		}
	
	
		/// <summary>
		/// Lista objeto
		/// </summary>						
		public List<OBJ_TB_NF_ITEM> SelecionarLista()
    	{
        	try
        	{
				DAO_DATABASE db = new DAO_DATABASE();
				List<OBJ_TB_NF_ITEM> lista = new List<OBJ_TB_NF_ITEM>();
				OBJ_TB_NF_ITEM obj = null;
				DataTable dt = new DataTable();
				DataSet ds = new DataSet();
				StringBuilder sb = new StringBuilder();
				sb.Append("Select * from  TB_NF_ITEM ");
        
	            ds = db.ExecuteQuery(sb.ToString());
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					
					foreach (DataRow dr in dt.Rows)
					{											
						obj = new OBJ_TB_NF_ITEM();
						Set(obj,dr);
						lista.Add(obj);
		
					}
				}
				return lista;
        	}
        	catch (Exception)
       		{
            	throw;
        	}

    	}
		
		/// <summary>
		/// Set Objeto
		/// </summary>	
		private void Set(OBJ_TB_NF_ITEM obj,DataRow dr)
		{
			obj.id = Convert.ToInt32(dr["id"]);						
			obj.id_nf = Convert.ToInt32(dr["id_nf"].ToString()) ;	
			obj.id_produto_servico = Convert.ToInt32(dr["id_produto_servico"].ToString()) ;	
			obj.id_tipo_item = Convert.ToInt32(dr["id_tipo_item"].ToString()) ;	
			obj.ean = dr["ean"] == DBNull.Value ?  string.Empty : dr["ean"].ToString() ;
			obj.descricao = dr["descricao"] == DBNull.Value ?  string.Empty : dr["descricao"].ToString() ;
			obj.id_ncm = Convert.ToInt32(dr["id_ncm"].ToString()) ;	
			obj.extipi = dr["extipi"] == DBNull.Value ?  string.Empty : dr["extipi"].ToString() ;
			obj.id_genero_produto_servico = Convert.ToInt32(dr["id_genero_produto_servico"].ToString()) ;	
			obj.cfop = Convert.ToInt32(dr["cfop"].ToString()) ;	
			obj.qtd = dr["qtd"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["qtd"].ToString()) ;
			obj.qtd_trib = dr["qtd_trib"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["qtd_trib"].ToString()) ;
			obj.id_unidade_medida = Convert.ToInt32(dr["id_unidade_medida"].ToString()) ;	
			obj.desc_unid_medida = dr["desc_unid_medida"] == DBNull.Value ?  string.Empty : dr["desc_unid_medida"].ToString() ;
			obj.desc_unid_medida_trib = dr["desc_unid_medida_trib"] == DBNull.Value ?  string.Empty : dr["desc_unid_medida_trib"].ToString() ;
			obj.valor_unitario = dr["valor_unitario"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_unitario"].ToString()) ;
			obj.valor_unitario_trib = dr["valor_unitario_trib"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_unitario_trib"].ToString()) ;
			obj.valor_frete = dr["valor_frete"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_frete"].ToString()) ;
			obj.valor_seguro = dr["valor_seguro"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_seguro"].ToString()) ;
			obj.valor_desconto = dr["valor_desconto"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_desconto"].ToString()) ;
			obj.valor_outro = dr["valor_outro"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_outro"].ToString()) ;
			obj.valor_total = dr["valor_total"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_total"].ToString()) ;
			obj.ind_total_nota = Convert.ToInt32(dr["ind_total_nota"].ToString()) ;	
			
		}						
				
		/// <summary>
		/// Selecionar Objeto Por ID
		/// </summary>	
		public OBJ_TB_NF_ITEM SelecionarObjetoPorId(int id)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				OBJ_TB_NF_ITEM obj = new OBJ_TB_NF_ITEM();
				DataSet ds = new DataSet();				
				DataTable dt = new DataTable();
				
				db.CreateParameter("@id", SqlDbType.Int, id);
				StringBuilder sb = new StringBuilder();
				sb.Append("SELECT * FROM tb_nf_item  Where id = @id");
				ds = db.ExecuteQuery(sb.ToString());				
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					
					foreach (DataRow dr in dt.Rows)
                	{
						obj = new OBJ_TB_NF_ITEM();
						Set(obj,dr);																			
					}
					return obj;
					
				}
				else
				{
					return null;	
				}			
			}
			catch (Exception)
       		{
            	throw;
        	}
			
		}
					
		
		
		/// <summary>
		/// Lista objeto Parametro Where OrderBy
		/// </summary>							
		public List<OBJ_TB_NF_ITEM> SelecionarLista(String Where, String OrderBy)
    	{
        	try
        	{
				DAO_DATABASE db = new DAO_DATABASE();
				OBJ_TB_NF_ITEM obj = null;
				DataSet ds = new DataSet();
				DataTable dt = new DataTable();
				List<OBJ_TB_NF_ITEM> lista = new List<OBJ_TB_NF_ITEM>();
				
				StringBuilder sb = new StringBuilder();
				db.CreateParameter("@Where",SqlDbType.VarChar, Where );
				db.CreateParameter("@OrderBy", SqlDbType.VarChar, OrderBy );				
				
				sb.Append("SELECT * ");
				sb.Append(" FROM TB_NF_ITEM");
				if(Where != string.Empty)				
				sb.Append(string.Format(" Where {0} ",Where));
				if(OrderBy != string.Empty)
				sb.Append(string.Format(" Order by {0}",OrderBy));
				
				ds = db.ExecuteQuery(sb.ToString());
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					foreach (DataRow dr in dt.Rows)
					{						
						obj = new OBJ_TB_NF_ITEM();
						Set(obj,dr);			
						lista.Add(obj);
					}
				}
				return lista;
        	}
        	catch (Exception)
       		{
            	throw;
        	}

    	}

		
		public DataSet Selecionar(String Where, String OrderBy, int PageIndex, int PageSize )
		{
				DAO_DATABASE db = new DAO_DATABASE();
				DataSet ds = new DataSet();
				
				StringBuilder sb = new StringBuilder();
				
				PageIndex = PageSize * PageIndex;
            	PageSize =  PageIndex + PageSize;
				
		      	if (OrderBy == string.Empty) OrderBy = "tb_nf_item.id";

            	sb.Append("SELECT * FROM ");
            	sb.Append(string.Format(" (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber,",OrderBy));
           		sb.Append(" * FROM   tb_nf_item ");
				if (Where != string.Empty)
				{
					sb.Append(string.Format(" WHERE {0}", Where));
				}
				sb.Append(" ) query  ");
				sb.Append(string.Format(" WHERE RowNumber BETWEEN {0} + 1 and {1}; ",PageIndex,PageSize));

            	sb.Append("SELECT count(*) FROM tb_nf_item");
				if (Where != string.Empty)
				{
					sb.Append(string.Format(" WHERE {0}", Where));
				}
				ds = db.ExecuteQuery(sb.ToString());
				
				if (!db.DataSetIsEmpty(ds))
            	{
                publico.PUB_PAGINACAO.CalcularTotalDePaginas(Convert.ToDecimal(ds.Tables[1].Rows[0][0].ToString()));
            	}

				
            	return ds;
								
		}
				
								
		public DataSet Selecionar(String Where, String OrderBy)
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();

            sb.Append("Select * from  TB_NF_ITEM ");
           if (Where != string.Empty)
            {
                db.CreateParameter("@Where", SqlDbType.VarChar, Where);
                sb.Append(string.Format(" Where  {0}",Where));
            }

            if (OrderBy != string.Empty)
            {
                db.CreateParameter("@OrderBy", SqlDbType.VarChar, OrderBy);
                sb.Append(string.Format(" Order By {0}", OrderBy));
            }

            ds = db.ExecuteQuery(sb.ToString());
            return ds;
        }		
		
		public DataSet Selecionar()
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();

            sb.Append("Select * from  TB_NF_ITEM Order By id");            
            ds = db.ExecuteQuery(sb.ToString());
            return ds;
        }		
		
		
		public DataSet ListaCampos()
        {

            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT TABLE_NAME, ORDINAL_POSITION, ");
            sb.Append(" COLUMN_NAME, DATA_TYPE, IS_NULLABLE ");
            sb.Append(" FROM INFORMATION_SCHEMA.COLUMNS");
            sb.Append(" WHERE TABLE_NAME = 'tb_nf_item'");
            ds = db.ExecuteQuery(sb.ToString());

            return ds;

        }

		public DataSet DropdownList()
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
			sb.Append("SELECT ");
			sb.Append(" id ");
				sb.Append(", id_nf ");  				
				sb.Append(", id_produto_servico ");  				
				sb.Append(", id_tipo_item ");  				
			sb.Append(" FROM tb_nf_item ");           
			sb.Append(" ORDER BY id_nf ");
            ds = db.ExecuteQuery(sb.ToString());

            // ds = db.ExecuteProcedure("USP_TB_NF_ITEM_DROPDOWNLIST");  
            return ds;
        }

        public DataSet DropdownList(String Where)
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            db.CreateParameter("@Where", SqlDbType.VarChar, Where);
			sb.Append("SELECT ");
			sb.Append(" id ");
			
			
			sb.Append(", id_nf ");  				
			
			
			sb.Append(", id_produto_servico ");  				
			
			
			sb.Append(", id_tipo_item ");  				
			sb.Append(" FROM tb_nf_item ");
			sb.Append(string.Format(" WHERE {0} ",Where));
			sb.Append(" ORDER BY id_nf ");
            ds = db.ExecuteQuery(sb.ToString());

            // ds = db.ExecuteProcedure("USP_TB_NF_ITEM_DROPDOWNLIST");
            return ds;
        }
		
		  public DataSet DropdownList(String Where, String OrderBy)
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            db.CreateParameter("@Where", SqlDbType.VarChar, Where);
			sb.Append("SELECT ");
			sb.Append(" id ");
			sb.Append(", id_nf ");  				
			sb.Append(", id_produto_servico ");  				
			sb.Append(", id_tipo_item ");  				
			sb.Append(" FROM tb_nf_item ");
			sb.Append(string.Format(" WHERE {0} ORDER BY {1}",Where,OrderBy));			
            ds = db.ExecuteQuery(sb.ToString());

            // ds = db.ExecuteProcedure("USP_TB_NF_ITEM_DROPDOWNLIST");
            return ds;
        }


          public void Serialize(List<OBJ_TB_NF_ITEM> obj, string arquivo)
          {

              try
              {
                  // C#
                  // Create file to save the data to
                  FileStream fs = new FileStream(arquivo, FileMode.Create);
                  // Create an XmlSerializer object to perform the serialization
                  XmlSerializer xs = new XmlSerializer(typeof(List<OBJ_TB_NF_ITEM>));
                  // Use the XmlSerializer object to serialize the data to the file
                  xs.Serialize(fs, obj);
                  // Close the file
                  fs.Close();
              }
              catch (Exception)
              {

                  throw;
              }



          }


          public List<OBJ_TB_NF_ITEM> Deserialize(string arquivo)
          {
              try
              {
                  // C#
                  // Open file to read the data from
                  FileStream fs = new FileStream(arquivo, FileMode.Open);
                  // Create an XmlSerializer object to perform the deserialization
                  XmlSerializer xs = new XmlSerializer(typeof(List<OBJ_TB_NF_ITEM>));
                  // Use the XmlSerializer object to deserialize the data from the file
                  List<OBJ_TB_NF_ITEM> obj = (List<OBJ_TB_NF_ITEM>)xs.Deserialize(fs);
                  // Close the file
                  fs.Close();

                  return obj;

              }
              catch (Exception)
              {

                  throw;
              }

          }

        public DataTable CriarDataTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(int)));
            dt.Columns.Add(new DataColumn("id_nf", typeof(int)));
            dt.Columns.Add(new DataColumn("id_produto_servico", typeof(int)));
            dt.Columns.Add(new DataColumn("id_tipo_item", typeof(int)));
            dt.Columns.Add(new DataColumn("ean", typeof(int)));
            dt.Columns.Add(new DataColumn("descricao", typeof(string)));
            dt.Columns.Add(new DataColumn("extipi", typeof(string))); 
            dt.Columns.Add(new DataColumn("id_ncm", typeof(int))); 
            dt.Columns.Add(new DataColumn("id_genero_produto_servico", typeof(int))); 
            dt.Columns.Add(new DataColumn("cfop", typeof(int))); 
            dt.Columns.Add(new DataColumn("id_unidade_medida", typeof(int))); 
            dt.Columns.Add(new DataColumn("qtd", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("qtd_trib", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("desc_unid_medida", typeof(string))); 
            dt.Columns.Add(new DataColumn("desc_unid_medida_trib", typeof(string))); 
            dt.Columns.Add(new DataColumn("valor_unitario", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("valor_unitario_trib", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("valor_frete", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("valor_seguro", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("valor_total", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("valor_desconto", typeof(decimal))); 
            dt.Columns.Add(new DataColumn("valor_outro", typeof(decimal)));
            dt.Columns.Add(new DataColumn("ind_total_nota", typeof(int)));    
 
            return dt;
        }

          /// <summary>
          /// Preeenche DataTable
          /// </summary>
          /// <param name="dt"></param>
          /// <param name="item"></param>
          /// <returns></returns>
          public DataTable PreencheDataTable(DataTable dt, OBJ_TB_NF_ITEM item)
          {

              DataRow dr = null;
              dr = dt.NewRow();

              dr["id"] = item.id;
              dr["id_nf"] = item.id_nf;
              dr["id_produto_servico"] = item.id_produto_servico;
              dr["id_tipo_item"] = item.id_tipo_item;
              dr["ean"] = item.ean.ToString();
              dr["descricao"] = item.descricao.ToString();
              dr["extipi"] = item.extipi.ToString();
              dr["id_ncm"] = item.id_ncm;
              dr["id_genero_produto_servico"] = item.id_genero_produto_servico;
              dr["cfop"] = item.cfop;
              dr["id_unidade_medida"] = item.id_unidade_medida;
              dr["qtd"] = item.qtd;
              dr["qtd_trib"] = item.qtd_trib;
              dr["desc_unid_medida"] = item.desc_unid_medida;
              dr["desc_unid_medida_trib"] = item.desc_unid_medida_trib;
              dr["valor_unitario"] = item.valor_unitario;
              dr["valor_unitario_trib"] = item.valor_unitario_trib;
              dr["valor_frete"] = item.valor_frete;
              dr["valor_seguro"] = item.valor_seguro;
              dr["valor_total"] = item.valor_total;
              dr["valor_desconto"] = item.valor_desconto;
              dr["valor_outro"] = item.valor_outro;
              dr["ind_total_nota"] = item.ind_total_nota;
 
              dt.Rows.Add(dr);

              return dt;
          }
	}
	#endregion
}

